/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DataAccess;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Home
 */
public class Bills {

    public static void insertBill(Entity.Bills b, ArrayList<Entity.BillSellDetails> details) {
        String sql = "insert into Bills(AccountID, CustomerID, Date, Discount, Payment) values(?, ?, ?, ?, ?)";
        try {
            PreparedStatement pst = DBUtil.getConnection(DBUtil.getInfoDB()).prepareCall(sql);
            pst.setInt(1, b.getAccountID());
            pst.setInt(2, b.getCustomerID());
            pst.setString(3, b.getDate());
            pst.setFloat(4, b.getDiscount());
            pst.setString(5, b.getPayment());
            pst.executeUpdate();

            b.setBillID(topid());
            System.out.println(b.getBillID());
            for (Entity.BillSellDetails bd : details) {
                DataAccess.BillSellDetails.insertBillDetail(b, bd);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Bills.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static int topid() {
        int id = 1;
        try {
            Connection cn = DBUtil.getConnection(DBUtil.getInfoDB());
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery("select top(1) BillID from Bills order by BillID Desc");
            while (rs.next()) {
                id = Integer.valueOf(rs.getString("BillID"));
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return id;
    }

    public static ArrayList<Entity.Bills> getAll() {
        try {
            PreparedStatement pst = DBUtil.getConnection(DBUtil.getInfoDB()).prepareStatement("select * from Bills");
            ResultSet rs = pst.executeQuery();

            ArrayList<Entity.Bills> bills = new ArrayList<Entity.Bills>();
            while (rs.next()) {
                Entity.Bills b = new Entity.Bills();
                b.setBillID(rs.getInt("BillID"));
                b.setAccountID(rs.getInt("AccountID"));
                b.setCustomerID(rs.getInt("CustomerID"));
                b.setDate(String.valueOf(rs.getDate("Date")));
                b.setDiscount(rs.getFloat("Discount"));
                b.setPayment(rs.getString("Payment"));
                b.setStatus(rs.getBoolean("Status"));
                bills.add(b);
            }
            return bills;
        } catch (Exception ex) {
            System.out.print(ex.getMessage());
            return new ArrayList<Entity.Bills>();
        }

    }

//    public static boolean readToTableBill(String sql, JTable tableName) {
//        try {
//            int nCount = 0;
//            DefaultTableModel dm = (DefaultTableModel) tableName.getModel();
//            for (int i = dm.getRowCount() - 1; i >= 0; i--) {
//                dm.removeRow(i);
//            }
//            Vector cols = new Vector();
//            Statement st = DBUtil.getConnection(DBUtil.getInfoDB()).createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//            ResultSet rs = st.executeQuery(sql);
//            ResultSetMetaData rm = rs.getMetaData();
//            for (int i = 1; i <= rm.getColumnCount(); i++) {
//                cols.add(rm.getColumnName(i));
//            }
//            dm.setColumnIdentifiers(cols);
//            while (rs.next()) {
//                Vector rows = new Vector();
////                for (int i = 1; i <= rm.getColumnCount(); i++) {
////                    rows.add(rs.getString(i));
////                    nCount++;
////                }
//                nCount++;
//                rows.addElement(rs.getInt(1));
//                rows.addElement(rs.getInt(2));
//                rows.addElement(rs.getInt(3));
//                rows.addElement(rs.getDate(4));
//                rows.addElement(rs.getFloat(5));
//                rows.addElement(rs.getString(6));
//                rows.addElement(rs.getBoolean(7));
//
//                dm.addRow(rows);
//            }
//            tableName.setModel(dm);
//            if (nCount > 0) {
//                return true;
//            } else {
//                return false;
//            }
//        } catch (SQLException ex) {
//            Logger.getLogger(TableAccess.class.getName()).log(Level.SEVERE, null, ex);
//            return false;
//        }
//
//    }
}
